<?php
require_once(dirname(__FILE__).'/service.php');

abstract class DataService extends Service {

    protected $db = null;
    protected $sqlParser = null;
    protected $config = null;

    protected function DataService()
    {
        $config = simplexml_load_file(CONFIG_FILE);
        $logSql = ($config->database->logsql == 'true');
        $debug =  ($config->database->debug == 'true');


        $this->sqlParser= new SafeSQL_PG();


        $this->db = NewADOConnection($config->database->type);
        $this->db->Connect($config->database->host, $config->database->username, $config->database->password, $config->database->name);


        if(!$this->db->IsConnected()){
            $this->getLogger()->error("No database connection!!!!!");
            $this->getLogger()->error($this->db);
        }


        $this->db->LogSQL($logSql);
        $this->db->debug = $debug;
        $this->db->SetFetchMode(ADODB_FETCH_ASSOC);
        //$this->db->Execute("set DateStyle='SQL, DMY'");

        //$this->db->Execute('show tables');
    }

    protected function getNowTimeStamp()
    {
        $timeStamp = $this->db->DBTimeStamp(time());
        $timeStamp = str_replace("'", "", $timeStamp);
        return($timeStamp);
    }

    protected function getLastInsertedID($table=null,$colum_sequence_name=null)
    {
        switch($this->db->databaseType){
            case 'postgres8':
            case 'postgres7':
            case 'postgres':
                if( $table && $colum_sequence_name )
                    $lastID = $this->db->GetOne("select currval('${table}_${colum_sequence_name}_seq');");
                else
                    $lastID = $this->db->Insert_ID( );
                break;
            default:
                $lastID = $this->db->Insert_ID( );
        }
        return $lastID;
    }

    public function executeInsert($table, $params, $colum_sequence_name = null)
    {

        $this->getLogger()->debug("executeInsert");
        $this->getLogger()->debug("table: " . print_r($table,true));
        $this->getLogger()->debug("colum_sequence_name: " . print_r($colum_sequence_name,true));
        $this->getLogger()->debug("params: " . print_r($params,true));


        unset($params[$colum_sequence_name]); //es autonumerico, lo saco fuera del query
        $result  = $this->db->AutoExecute($table, $params,'INSERT');
        $this->getLogger()->debug("AutoExecute: " . print_r($result,true));
        
		$pk = $this->getPrimaryKeyName($table);
		
		if( $colum_sequence_name != null || $pk==null )
            //$result = $this->db->GetOne("select currval('${table}_${colum_sequence_name}_seq');");
            $result = $this->getLastInsertedID($table,$colum_sequence_name);
        else
            $result = $params[$this->getPrimaryKeyName($table)];

        return($result);
    }

    public function executeUpdate($table,$params,$where)
    {
        $result = $this->db->AutoExecute($table, $params, 'UPDATE', $where);
        return($result);
    }


    // Deprecated, usar SafeGetAll !!!!!!!!!!!!!!
    public function GetAll($sql,$limit = null,$start = null,$sort = null,$dir = null)
    {

        if(func_num_args()>5){
            $sql_params = array();
            for($i=5; $i<func_num_args(); $i++){
                $param = func_get_arg($i);
                if(is_array($param))
                    $sql_params = array_merge($sql_params,$param);
                else
                    $sql_params = array_merge($sql_params,(array)$param);
            }

            $safe_sql = $this->sqlParser->query($sql,$sql_params);
        }
        else
            $safe_sql = $sql;

        $sql_count = "select count(*) as cantidad from (${safe_sql}) as temporal";
        $results = $this->db->GetOne($sql_count);

        if($sort){
            $sql= "${sql} order by ${sort}";
            if($dir)
                $sql= "${sql} ${dir}";
        }

        if($limit!=null &&  $start !=null )
            $sql = "${sql} limit ${limit} offset ${start}";
        else {
            if($limit!=null)
                $sql = "${sql} limit ${limit} ";
        }

        $rows = array();
        $rows = $this->db->GetAll($safe_sql);

        $result = array('rows'=>$rows, 'results' => $results);
        return($result);
    }

    // Nueva función para ejecutar consultas en la db, características:
    //      + Es Safe
    //      + Los parámetros se pueden pasar como array, o como variables, un uno o mas argumentos, pero siempre deben estar ORDENADOS!
    //      + Si se le pasan parámetros con las claves sort,dir,limit,start se pueden ordenar y/o paginar los resultados (estos parámetros no tienen ningún orden en particular)
    //      + Si se le pasa el parámetro onlyCount=true solo devuelve la cantidad de registros correspodientes a la consultas y NO REALIZA la consulta en si.
    //      + Siempre retorna:
    //              - rows: Resultados de la consulta (null si se paso onlyCount=true)
    //              - results: Cantidad Total de resultados, sin tener en cuenta la paginación.
    public function SafeGetAll($sql)
    {

        $sql_params=array();
        $aux_params = array();
        
        if(func_num_args()>1){
            for($i=1; $i<func_num_args(); $i++)
            {
                $param = func_get_arg($i);
                if(!is_array($param))
                {
                    array_push($aux_params,$param);
                    $sql_params = array_merge($sql_params,$aux_params);
                    $aux_params = array();
                }
                else
                    $sql_params = array_merge($sql_params,$param);
            }
        }

        if($sql_params!=null && !empty($sql_params))
        {

            if(!is_array($sql_params))
                $sql_params = (array)$sql_params;

            if(array_key_exists('limit',$sql_params))
            {
                $limit = $sql_params['limit'];
                unset($sql_params['limit']);
            }

            if(array_key_exists('start',$sql_params))
            {
                $start = $sql_params['start'];
                unset($sql_params['start']);
            }

            if(array_key_exists('sort',$sql_params))
            {
                $sort = $sql_params['sort'];
                unset($sql_params['sort']);
            }

            if(array_key_exists('dir',$sql_params))
            {
                $dir = $sql_params['dir'];
                unset($sql_params['dir']);
            }

            if(array_key_exists('countOnly',$sql_params))
            {
                $countOnly = $sql_params['countOnly'];
                unset($sql_params['countOnly']);
            }
        }

        $safe_sql = $this->sqlParser->query($sql,$sql_params);
        $sql_count = "select count(*) as cantidad from (${safe_sql}) as temporal";

        $this->getLogger()->debug($sql);
        $this->getLogger()->debug($sql_params);
        $this->getLogger()->debug($sql_count);

        $results = $this->db->GetOne($sql_count);

        if( !isset($countOnly) || ( isset($countOnly) && $countOnly!=true ) )
        {

            $other_params = array();

            // sort, dir
            if(isset($sort) && $sort!=null)
            {
                $safe_sql .= "[ order by %S]";
                array_push($other_params,$sort);
                if(isset($dir) && $dir!=null)
                {
                    $safe_sql .= "[ %S]";
                    array_push($other_params,$dir);
                }
            }

            //limit, start
            if(isset($limit) && $limit!=null)
            {
                $safe_sql .= "[ limit %I]";
                array_push($other_params,$limit);
                if(isset($start) && $start!=null)
                {
                    $safe_sql .= "[ offset %I]";
                    array_push($other_params,$start);
                }
            }

            $safe_sql = $this->sqlParser->query($safe_sql,$other_params);

            $rows = array();

            $this->getLogger()->debug($safe_sql);
            $rows = $this->db->GetAll($safe_sql);

        }
        else
            $rows = null;

        $result = array('rows'=>$rows, 'results' => $results);
        return($result);
    }


    public function SafeGetOne($sql)
    {

        $sql_params=array();
        if(func_num_args()>1){
            $aux_params = array();
            for($i=1; $i<func_num_args(); $i++){
                $param = func_get_arg($i);
                if(!is_array($param)){
                    array_push($aux_params,$param);
                    $sql_params = array_merge($sql_params,$aux_params);
                    $aux_params = array();
                }
                else
                    $sql_params = array_merge($sql_params,$param);
            }
        }

        $safe_sql = $this->sqlParser->query($sql,$sql_params);

        return $this->db->GetOne($safe_sql);
    }

    public function SafeGetRow($sql)
    {

        $sql_params=array();
        if(func_num_args()>1){
            $aux_params = array();
            for($i=1; $i<func_num_args(); $i++){
                $param = func_get_arg($i);
                if(!is_array($param)){
                    array_push($aux_params,$param);
                    $sql_params = array_merge($sql_params,$aux_params);
                    $aux_params = array();
                }
                else
                    $sql_params = array_merge($sql_params,$param);
            }
        }

        $safe_sql = $this->sqlParser->query($sql,$sql_params);

        $this->getLogger()->debug($safe_sql);

        return $this->db->GetRow($safe_sql);
    }

    public function SafeExec($sql)
    {

        $sql_params=array();
        if(func_num_args()>1){
            $aux_params = array();
            for($i=1; $i<func_num_args(); $i++){
                $param = func_get_arg($i);
                if(!is_array($param)){
                    array_push($aux_params,$param);
                    $sql_params = array_merge($sql_params,$aux_params);
                    $aux_params = array();
                }
                else
                    $sql_params = array_merge($sql_params,$param);
            }
        }

        $safe_sql = $this->sqlParser->query($sql,$sql_params);
        return $this->db->Execute($safe_sql);
    }

    public function affectedRows()
    {
        return $this->db->Affected_Rows();
    }

    public function beginTrans()
    {
        $this->db->BeginTrans();
    }

    public function commitTrans()
    {
        $this->db->CommitTrans();
    }

    public function rollbackTrans()
    {
        $this->db->RollbackTrans();
    }


    /*****************************************
                Smart Transactions
    *****************************************/
    public function StartTrans()
    {
        $this->db->StartTrans();
    }

    public function FailTrans()
    {
        $this->db->FailTrans();
    }

    public function CompleteTrans()
    {
        $this->db->CompleteTrans();
    }

    public function HasFailedTrans()
    {
        return $this->db->HasFailedTrans();
    }

    public function getSharedTipos()
    {
        return($this->db->GetAll("select * from shared_tipos order by categoria,descripcion"));
    }

    public function getPrimaryKeyName($tableName)
    {
        $this->getLogger()->debug('---- getPrimaryKeyName ----');
        $this->getLogger()->debug($tableName);
        $columns = $this->db->MetaColumns($tableName,true);
        $this->getLogger()->debug($columns);
        foreach($columns as $c)
            if(isset($c->primary_key) && $c->primary_key)
                return($c->name);
        return(null);
    }
}

?>